/** Addressbook loader :

    The program will use the PreparedStatement to load a set of randomly generated
persons in the address book. 

*/

import java.sql.*;
import java.io.*;
import person.*;

public class JDBC2Loader {

	Connection cnn = null;
	PreparedStatement  pStmt = null;
	Statement  stmt = null;
	ResultSet  res = null ;
	ResultSetMetaData meta = null;

	String url = "jdbc:oracle:thin:@prover.cs.csubak.edu:1521:PROVERDB",
		 user="cs440", passwd="student1";

	public JDBC2Loader() {
	   try {
             	try { Class.forName("oracle.jdbc.driver.OracleDriver"); }
		catch (ClassNotFoundException ee) { ee.printStackTrace(); System.exit(-1); }

		cnn = DriverManager.getConnection(url, user, passwd);
		pStmt = cnn.prepareStatement(
		   "INSERT INTO AddressBook(lastname, firstname, phone1, phone2) values(?, ?, ?, ?)" );
	   } catch (SQLException e ) { e.printStackTrace(); System.exit(-1); }
	}

	public static void main(String argv[]) {
		JDBC2Loader ldr = new JDBC2Loader(); // get connected, and create stmt.
		boolean succ;
		ldr.deleteRecords();

		for ( int i = 0; i < 20; i++ ) {
			succ = ldr.insertNextPerson();
			if ( succ )
				System.out.println(" Record " + i + " is inserted successfully.");
			else
				System.out.println(" Record " + i + " is NOT inserted successfully.");
		}
		ldr.displayResultSet();
		ldr.quit();
	}

        void deleteRecords() {
	   try {
		stmt = cnn.createStatement();
		int  cnt = stmt.executeUpdate("delete from cs440.addressbook");
		cnn.commit();
		System.out.println( cnt + " records deleted successfully!");
		stmt.close();
	   } catch (SQLException e) { e.printStackTrace(); }
	}

	boolean insertNextPerson() {
	   Person p = new Person();
	   try {
		pStmt.clearParameters();
		pStmt.setString(1, p.getLastName());
		pStmt.setString(2, p.getFirstName());
		pStmt.setString(3, ScreenIO.formatPhone((long) (Math.random()* 10000000)));
		pStmt.setString(4, ScreenIO.formatPhone((long) (Math.random()* 10000000)));
		pStmt.execute();
		return true;
	   } catch (SQLException e) { e.printStackTrace();  return false; }
	}

	void displayResultSet() {
	   try {
		stmt = cnn.createStatement();
		res = stmt.executeQuery("select lastname, firstname, phone1 \"Home Phone\", phone2 \"Work Phone\" from addressbook");
		meta = res.getMetaData();
		int colCnt = meta.getColumnCount();
		displayColumnHeaders(colCnt);
		int recCount = 0;
		while ( res.next() ) {
			recCount ++;
			for ( int i = 1; i <= colCnt; i ++ )
				System.out.print(ScreenIO.expandString( res.getString(i), 15 )) ;
			System.out.println();
			if ( recCount % 20 == 0 && ! ScreenIO.continueToList()) return;
		}	
	   } catch (SQLException e) { e.printStackTrace();  /*System.exit(-1);*/ }
	     catch (NullPointerException e ) {}
	}

	void displayColumnHeaders(int cnt) {
	   try {
		for ( int i = 1; i <= cnt; i ++ )
			System.out.print( ScreenIO.expandString(meta.getColumnName(i), 15));
		System.out.println();

		for ( int i = 1; i <= cnt; i ++ )
			System.out.print( ScreenIO.expandString("===========", 15));
		System.out.println();
	   } catch (SQLException e ) {}
	
	}
	protected void quit() {
	   try {
		pStmt.close();
		if ( stmt != null ) stmt.close();
		if ( res != null ) res.close();
		cnn.close();
		System.exit(0);
	   } catch (SQLException e) {}
	   try { super.finalize(); } catch (Throwable e) {}
	}
}
